package edu.hku.c3330.hkucompanion.manager;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import edu.hku.c3330.hkucompanion.manager.DataManager.AssessmentDbConstants;
import edu.hku.c3330.hkucompanion.manager.DataManager.CourseDbConstants;
import edu.hku.c3330.hkucompanion.manager.DataManager.EventDbConstants;
import edu.hku.c3330.hkucompanion.manager.DataManager.ReferenceDbConstants;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class CourseManager
{
	static Context context;

	public static void setContext(Context context)
	{
		CourseManager.context = context;
	}
		
	public static long updateCourse(Course course)
	{

		SQLiteDatabase db = DataManager.getInstance(context).getWritableDatabase();		
		ContentValues courseContent = new ContentValues();
		courseContent.put(CourseDbConstants.NAME, course.name);
		courseContent.put(CourseDbConstants.DESC, course.description);
		courseContent.put(CourseDbConstants.INSTRUCTOR, course.instructor);
		courseContent.put(CourseDbConstants.COURSE_CODE, course.courseCode);
		
		long courseId = course.courseId;
		
		if (courseId < 0)
		{
			courseId = db.insert(CourseDbConstants.TABLE_NAME, null, courseContent);
			course.setDatabaseCourseId(courseId);
		}
		else
		{
			db.update(
					CourseDbConstants.TABLE_NAME,
					courseContent,
					CourseDbConstants._ID + " = ?", 
					new String[]{Long.valueOf(courseId).toString()});
		}

		for (Assessment assessment : course.assessments)
		{
			updateAssessment(courseId, assessment);
		}
		return courseId;
	}
	
	public static void updateAssessment(long courseId, Assessment assessment)
	{	
		
		SQLiteDatabase db = DataManager.getInstance(context).getWritableDatabase();		
		
		long eventId = assessment._id;
		long assessmentId = assessment.assessmentId;
		if (eventId < 0)
		{
			EventManager.updateEvent(assessment);
			eventId =  assessment._id;
		}
		

		ContentValues assessmentContent = new ContentValues();
		assessmentContent.put(AssessmentDbConstants.COURSE_ID, courseId);
		assessmentContent.put(AssessmentDbConstants.EVENT_ID, eventId);
		assessmentContent.put(AssessmentDbConstants.TYPE, assessment.type.toString());
		
		if (assessmentId < 0)
		{
			assessmentId = db.insert(AssessmentDbConstants.TABLE_NAME, null, assessmentContent);
			assessment.setDatabaseId(courseId, eventId, assessmentId);
		}
		else
		{
			db.update(
					AssessmentDbConstants.TABLE_NAME,
					assessmentContent,
					AssessmentDbConstants._ID + " = ?", 
					new String[]{Long.valueOf(assessmentId).toString()});
		}
		
		updateReferences(assessment);
	}
	
	public static void updateReferences(Assessment assessment)
	{
		SQLiteDatabase db = DataManager.getInstance(context).getWritableDatabase();		

		for (String reference : assessment.getReferences())
		{
			ContentValues referenceContent = new ContentValues();
			referenceContent.put(ReferenceDbConstants.ASSESSMENT_ID, assessment.assessmentId);
			referenceContent.put(ReferenceDbConstants.REFERENCE, reference);
			
			long referenceId = assessment.references.get(reference);
			if (referenceId < 0)
			{
				referenceId = db.insert(ReferenceDbConstants.TABLE_NAME, null, referenceContent);
				assessment.putReference(reference, referenceId);
			}
			else
			{
				db.update(
						ReferenceDbConstants.TABLE_NAME,
						referenceContent,
						ReferenceDbConstants._ID + " = ?",
						new String[]{Long.valueOf(referenceId).toString()});
			}
			
		}

	}
		
	public static List<Course> getAllCourses()
	{
		SQLiteDatabase db = DataManager.getInstance(context).getReadableDatabase();
		Cursor cur  = db.query(
				CourseDbConstants.TABLE_NAME,
				new String[]{
					CourseDbConstants._ID,
					CourseDbConstants.NAME,
					CourseDbConstants.DESC,
					CourseDbConstants.INSTRUCTOR,
					CourseDbConstants.COURSE_CODE,
				},
				null,null, null, null, CourseDbConstants.COURSE_CODE + " ASC");		
			
			return extractCoursesFromCursor(db, cur);
	}
	
	
	public static Course getCourseById(long courseId)
	{
		SQLiteDatabase db = DataManager.getInstance(context).getReadableDatabase();
		Cursor cur = db.query(
				CourseDbConstants.TABLE_NAME,
				new String[]{
					CourseDbConstants._ID,
					CourseDbConstants.NAME,
					CourseDbConstants.DESC,
					CourseDbConstants.INSTRUCTOR,
					CourseDbConstants.COURSE_CODE,
				},
				CourseDbConstants._ID + " = ?",
				new String[]{Long.valueOf(courseId).toString()}, 
				null, null, CourseDbConstants.COURSE_CODE + " ASC");		
			
		List<Course> courses = extractCoursesFromCursor(db,cur);
		if (courses.size() > 0)
		{
			return courses.get(0);
		}
		else
		{
			return null;
		}
	}
	
	public static Course getCourseByCourseCode(String courseCode)
	{
		SQLiteDatabase db = DataManager.getInstance(context).getReadableDatabase();
		Cursor cur = db.query(
				CourseDbConstants.TABLE_NAME,
				new String[]{
					CourseDbConstants._ID,
					CourseDbConstants.NAME,
					CourseDbConstants.DESC,
					CourseDbConstants.INSTRUCTOR,
					CourseDbConstants.COURSE_CODE,
				},
				CourseDbConstants.COURSE_CODE + " = ?",
				new String[]{courseCode}, 
				null, null, CourseDbConstants.COURSE_CODE + " ASC");		
			
		List<Course> courses = extractCoursesFromCursor(db,cur);
		if (courses.size() > 0)
		{
			return courses.get(0);
		}
		else
		{
			return null;
		}
	}
	
	private static List<Course> extractCoursesFromCursor(SQLiteDatabase db, Cursor cur)
	{
		List<Course> courses = new ArrayList<Course>();
		if (cur.moveToFirst())
		do
		{
			long courseId = cur.getLong(cur.getColumnIndex(CourseDbConstants._ID));
			String name = cur.getString(cur.getColumnIndex(CourseDbConstants.NAME));
			String desc = cur.getString(cur.getColumnIndex(CourseDbConstants.DESC));
			String instructor = cur.getString(cur.getColumnIndex(CourseDbConstants.INSTRUCTOR));
			String courseCode = cur.getString(cur.getColumnIndex(CourseDbConstants.COURSE_CODE));
			Course course = new Course(name, desc, instructor, courseCode);
			course.setAssessments(getAssessmentsByCourseId(db,courseId));
			course.setDatabaseCourseId(courseId);
			courses.add(course);
		} while (cur.moveToNext());
		return courses;
	}

	private static List<Assessment> getAssessmentsByCourseId(SQLiteDatabase db, long courseId)
	{
		Cursor cur  = db.query(
			AssessmentDbConstants.TABLE_NAME,
			new String[]{
				AssessmentDbConstants._ID,
				AssessmentDbConstants.COURSE_ID,
				AssessmentDbConstants.EVENT_ID,
				AssessmentDbConstants.TYPE,
			},
			AssessmentDbConstants.COURSE_ID + " = ?", 
			new String[]
			{
				Long.valueOf(courseId).toString(),
			}, null, null, null);
				
		return extractAssessmentsFromCursor(db,cur);
	}
		
	public static Assessment getAssessmentByAssessmentId(long assessmentId)
	{
		SQLiteDatabase db = DataManager.getInstance(context).getReadableDatabase();
		Cursor cur  = db.query(
				AssessmentDbConstants.TABLE_NAME,
				new String[]{
					AssessmentDbConstants._ID,
					AssessmentDbConstants.COURSE_ID,
					AssessmentDbConstants.EVENT_ID,
					AssessmentDbConstants.TYPE,
				},
				AssessmentDbConstants._ID + " = ?", 
				new String[]
				{
					Long.valueOf(assessmentId).toString(),
				}, null, null, null);
			

			List<Assessment> assessments = extractAssessmentsFromCursor(db,cur);
			
			if (assessments.size() > 0)
			{
				return assessments.get(0);
			}
			else
			{
				return null;
			}
	}
		
	public static Assessment getAssessmentByEventId(long eventId)
	{
		SQLiteDatabase db = DataManager.getInstance(context).getReadableDatabase();
		Cursor cur  = db.query(
			AssessmentDbConstants.TABLE_NAME,
			new String[]{
				AssessmentDbConstants._ID,
				AssessmentDbConstants.COURSE_ID,
				AssessmentDbConstants.EVENT_ID,
				AssessmentDbConstants.TYPE,
			},
			AssessmentDbConstants.EVENT_ID + " = ?", 
			new String[]
			{
				Long.valueOf(eventId).toString(),
			}, null, null, null);
		

		List<Assessment> assessments = extractAssessmentsFromCursor(db,cur);
		
		if (assessments.size() > 0)
		{
			return assessments.get(0);
		}
		else
		{
			return null;
		}
	}
	
	private static List<Assessment> extractAssessmentsFromCursor(SQLiteDatabase db, Cursor cur)
	{
		List<Assessment> assessments = new ArrayList<Assessment>();
		if (cur.moveToFirst())
		do
		{
			long assessmentId = cur.getLong(cur.getColumnIndex(AssessmentDbConstants._ID));
			long eventId = cur.getLong(cur.getColumnIndex(AssessmentDbConstants.EVENT_ID));
			long courseId = cur.getLong(cur.getColumnIndex(AssessmentDbConstants.COURSE_ID));
			String type = cur.getString(cur.getColumnIndex(AssessmentDbConstants.TYPE));
			
			Event event = EventManager.getEventById(eventId); 
			Map<String, Long> references = getReferencesByAssessmentId(db, assessmentId);
			Assessment assessment = new Assessment(event, Assessment.AssessmentType.valueOf(type), references);
			assessment.setDatabaseId(courseId, eventId, assessmentId);
			assessments.add(assessment);
		} while (cur.moveToNext());
		return assessments;
	}
	
	private static Map<String, Long> getReferencesByAssessmentId(SQLiteDatabase db, long assessmentId)
	{
		Cursor cur  = db.query(
			ReferenceDbConstants.TABLE_NAME,
			new String[]{
				ReferenceDbConstants._ID,
				ReferenceDbConstants.ASSESSMENT_ID,
				ReferenceDbConstants.REFERENCE
			},
			ReferenceDbConstants.ASSESSMENT_ID + " = ?", 
			new String[]
			{
				Long.valueOf(assessmentId).toString(),
			}, null, null, null);
		
		Map<String, Long> refs = new HashMap<String, Long> ();
		if (cur.moveToFirst())
		do
		{
			String ref = cur.getString(cur.getColumnIndex(ReferenceDbConstants.REFERENCE));
			Long id = cur.getLong(cur.getColumnIndex(ReferenceDbConstants._ID));
			refs.put(ref, id);
		} while (cur.moveToNext());	
		
		return refs;
	}
	
	public static void removeCourse(Course course)
	{
		if (course.courseId >= 0)
		{
			SQLiteDatabase db = DataManager.getInstance(context).getWritableDatabase();
			db.delete(
					CourseDbConstants.TABLE_NAME, 
					CourseDbConstants._ID + " = ?", 
					new String[]{Long.valueOf(course.courseId).toString()}
				);
			for (Assessment assessment : course.assessments)
			{
				removeAssessment(assessment);
			}
		}
	}
	
	public static void removeAssessment(Assessment assessment)
	{
		if (assessment.assessmentId  >= 0)
		{
			SQLiteDatabase db = DataManager.getInstance(context).getWritableDatabase();
			EventManager.removeEvent(assessment);
			db.delete(
					AssessmentDbConstants.TABLE_NAME, 
					AssessmentDbConstants._ID + " = ?", 
					new String[]{Long.valueOf(assessment.assessmentId).toString()}
				);
			db.delete(
					ReferenceDbConstants.TABLE_NAME, 
					ReferenceDbConstants.ASSESSMENT_ID + " = ?", 
					new String[]{Long.valueOf(assessment.assessmentId).toString()}
				);
		}
	}
	
	public static void removeAllEntries()
	{
		SQLiteDatabase db = DataManager.getInstance(context).getWritableDatabase();
		db.delete(
				EventDbConstants.TABLE_NAME, 
				null, null
			);
	}
}
